{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# **Homework**: Data talks club data engineering zoomcamp Data loading workshop\n",
        "\n",
        "Hello folks, let's practice what we learned - Loading data with the best practices of data engineering.\n",
        "\n",
        "Here are the exercises we will do\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "mrTFv5nPClXh"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 1. Use a generator\n",
        "\n",
        "Remember the concept of generator? Let's practice using them to futher our understanding of how they work.\n",
        "\n",
        "Let's define a generator and then run it as practice.\n",
        "\n",
        "**Answer the following questions:**\n",
        "\n",
        "- **Question 1: What is the sum of the outputs of the generator for limit = 5?**\n",
        "- **Question 2: What is the 13th number yielded**\n",
        "\n",
        "I suggest practicing these questions without GPT as the purpose is to further your learning."
      ],
      "metadata": {
        "id": "wLF4iXf-NR7t"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def square_root_generator(limit):\n",
        "    n = 1\n",
        "    while n <= limit:\n",
        "        yield n ** 0.5\n",
        "        n += 1\n",
        "\n",
        "# Example usage:\n",
        "limit = 5\n",
        "generator = square_root_generator(limit)\n",
        "\n",
        "for sqrt_value in generator:\n",
        "    print(sqrt_value)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wLng-bDJN4jf",
        "outputId": "547683cb-5f56-4815-a903-d0d9578eb1f9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1.0\n",
            "1.4142135623730951\n",
            "1.7320508075688772\n",
            "2.0\n",
            "2.23606797749979\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [],
      "metadata": {
        "id": "xbe3q55zN43j"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 2. Append a generator to a table with existing data\n",
        "\n",
        "\n",
        "Below you have 2 generators. You will be tasked to load them to duckdb and answer some questions from the data\n",
        "\n",
        "1. Load the first generator and calculate the sum of ages of all people. Make sure to only load it once.\n",
        "2. Append the second generator to the same table as the first.\n",
        "3. **After correctly appending the data, calculate the sum of all ages of people.**\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "vjWhILzGJMpK"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2MoaQcdLBEk6",
        "outputId": "d2b93dc1-d83f-44ea-aeff-fdf51d75f7aa"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "{'ID': 1, 'Name': 'Person_1', 'Age': 26, 'City': 'City_A'}\n",
            "{'ID': 2, 'Name': 'Person_2', 'Age': 27, 'City': 'City_A'}\n",
            "{'ID': 3, 'Name': 'Person_3', 'Age': 28, 'City': 'City_A'}\n",
            "{'ID': 4, 'Name': 'Person_4', 'Age': 29, 'City': 'City_A'}\n",
            "{'ID': 5, 'Name': 'Person_5', 'Age': 30, 'City': 'City_A'}\n",
            "{'ID': 3, 'Name': 'Person_3', 'Age': 33, 'City': 'City_B', 'Occupation': 'Job_3'}\n",
            "{'ID': 4, 'Name': 'Person_4', 'Age': 34, 'City': 'City_B', 'Occupation': 'Job_4'}\n",
            "{'ID': 5, 'Name': 'Person_5', 'Age': 35, 'City': 'City_B', 'Occupation': 'Job_5'}\n",
            "{'ID': 6, 'Name': 'Person_6', 'Age': 36, 'City': 'City_B', 'Occupation': 'Job_6'}\n",
            "{'ID': 7, 'Name': 'Person_7', 'Age': 37, 'City': 'City_B', 'Occupation': 'Job_7'}\n",
            "{'ID': 8, 'Name': 'Person_8', 'Age': 38, 'City': 'City_B', 'Occupation': 'Job_8'}\n"
          ]
        }
      ],
      "source": [
        "def people_1():\n",
        "    for i in range(1, 6):\n",
        "        yield {\"ID\": i, \"Name\": f\"Person_{i}\", \"Age\": 25 + i, \"City\": \"City_A\"}\n",
        "\n",
        "for person in people_1():\n",
        "    print(person)\n",
        "\n",
        "\n",
        "def people_2():\n",
        "    for i in range(3, 9):\n",
        "        yield {\"ID\": i, \"Name\": f\"Person_{i}\", \"Age\": 30 + i, \"City\": \"City_B\", \"Occupation\": f\"Job_{i}\"}\n",
        "\n",
        "\n",
        "for person in people_2():\n",
        "    print(person)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [],
      "metadata": {
        "id": "vtdTIm4fvQCN"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 3. Merge a generator\n",
        "\n",
        "Re-use the generators from Exercise 2.\n",
        "\n",
        "A table's primary key needs to be created from the start, so load your data to a new table with primary key ID.\n",
        "\n",
        "Load your first generator first, and then load the second one with merge. Since they have overlapping IDs, some of the records from the first load should be replaced by the ones from the second load.\n",
        "\n",
        "After loading, you should have a total of 8 records, and ID 3 should have age 33.\n",
        "\n",
        "Question: **Calculate the sum of ages of all the people loaded as described above.**\n"
      ],
      "metadata": {
        "id": "pY4cFAWOSwN1"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Solution: First make sure that the following modules are installed:"
      ],
      "metadata": {
        "id": "kKB2GTB9oVjr"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Install the dependencies\n",
        "%%capture\n",
        "!pip install dlt[duckdb]"
      ],
      "metadata": {
        "id": "xTVvtyqrfVNq"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Solutions\n",
        "\n",
        "You can use these solutions to self check your results, or to check how the answer can be obtained if you get stuck."
      ],
      "metadata": {
        "id": "kUG4DNYGb5dF"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "ks6Sh_jBJWdh"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Solution 1"
      ],
      "metadata": {
        "id": "U61tgQaYb8Yt"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def sum_of_generator_outputs(generator, limit):\n",
        "    return sum(next(generator) for _ in range(limit))\n",
        "\n",
        "# Example usage:\n",
        "limit_1 = 5\n",
        "generator_1 = square_root_generator(limit_1)\n",
        "result_1 = sum_of_generator_outputs(generator_1, limit_1)\n",
        "print(f\"The sum of the outputs for limit={limit_1} is: {result_1}\")\n",
        "\n",
        "\n",
        "def nth_yielded_number(generator, n):\n",
        "    for _ in range(n - 1):\n",
        "        next(generator)\n",
        "    return next(generator)\n",
        "\n",
        "# Example usage:\n",
        "n = 13\n",
        "generator_2 = square_root_generator(n)\n",
        "result_2 = nth_yielded_number(generator_2, n)\n",
        "print(f\"The {n}th number yielded is: {result_2}\")\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Roc3y_lSTSfn",
        "outputId": "f03d348e-cdfa-44d0-e5f2-276db6af1cf5"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "The sum of the outputs for limit=5 is: 8.382332347441762\n",
            "The 13th number yielded is: 3.605551275463989\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Solution 2: Append a generator\n",
        "\n",
        "Load your first generator first, and then load the second one using the \"append\" operation. Since they have overlapping IDs, some records will appear multiple times.\n",
        "\n",
        "After loading, you should have a total of 11 records.\n",
        "\n",
        "Question: Calculate the sum of ages of all the people loaded as described above"
      ],
      "metadata": {
        "id": "M3PJYca2TIw8"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Importing the DLT library\n",
        "import dlt\n",
        "\n",
        "# Create a DLT pipeline for the first generator `people_1`\n",
        "# The pipeline is set to load data into a DuckDB database with the dataset named 'people'\n",
        "people_1_pipeline = dlt.pipeline(destination='duckdb', dataset_name='people')\n",
        "\n",
        "# Run the pipeline for the first generator, creating or replacing the table 'people'\n",
        "info = people_1_pipeline.run(people_1(),\n",
        "                             table_name=\"people\",\n",
        "                             write_disposition=\"replace\")\n",
        "\n",
        "print(f\"{info}\\n\\n\")\n",
        "\n",
        "\n",
        "# Create a second DLT pipeline for the generator `people_2`, targeting the same DuckDB database and dataset\n",
        "people_2_pipeline = dlt.pipeline(destination='duckdb', dataset_name='people')\n",
        "\n",
        "# Run the second pipeline, appending data from `people_2` to the existing 'people' table\n",
        "info = people_2_pipeline.run(people_2(),\n",
        "                             table_name=\"people\",\n",
        "                             write_disposition=\"append\")\n",
        "\n",
        "print(f\"{info}\\n\\n\")\n",
        "\n",
        "\n",
        "# Importing the DuckDB library\n",
        "import duckdb\n",
        "\n",
        "# Connect to the DuckDB database created by the first generator\n",
        "conn = duckdb.connect(f\"{people_1_pipeline.pipeline_name}.duckdb\")\n",
        "\n",
        "# Setting the search path to the dataset 'people' and displaying available tables\n",
        "conn.sql(f\"SET search_path = '{people_1_pipeline.dataset_name}'\")\n",
        "print('Loaded tables: ')\n",
        "display(conn.sql(\"show tables\"))\n",
        "\n",
        "\n",
        "# Fetching the appended data from the 'people' table and displaying it\n",
        "data = conn.sql(\"SELECT * FROM people\").df()\n",
        "display(data)\n",
        "\n",
        "# Calculate the sum of ages from the combined data of `people_1` and `people_2` in the 'people' table\n",
        "sum_of_ages_p1_p2 = conn.execute(\"SELECT SUM(age) FROM people\").fetchone()[0]\n",
        "print(f\"\\n\\nSum of ages from generators `people_1()` and `people_2()` combined: {sum_of_ages_p1_p2}\")\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 841
        },
        "id": "0u2mtndkTLpk",
        "outputId": "d5d253de-4502-42bf-ac89-08e0a7065d85"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Pipeline dlt_colab_kernel_launcher load step completed in 0.59 seconds\n",
            "1 load package(s) were loaded to destination duckdb and into dataset people\n",
            "The duckdb destination used duckdb:////content/dlt_colab_kernel_launcher.duckdb location to store data\n",
            "Load package 1706029306.7456656 is LOADED and contains no failed jobs\n",
            "\n",
            "\n",
            "Pipeline dlt_colab_kernel_launcher load step completed in 0.43 seconds\n",
            "1 load package(s) were loaded to destination duckdb and into dataset people\n",
            "The duckdb destination used duckdb:////content/dlt_colab_kernel_launcher.duckdb location to store data\n",
            "Load package 1706029307.9851513 is LOADED and contains no failed jobs\n",
            "\n",
            "\n",
            "Loaded tables: \n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "┌─────────────────────┐\n",
              "│        name         │\n",
              "│       varchar       │\n",
              "├─────────────────────┤\n",
              "│ _dlt_loads          │\n",
              "│ _dlt_pipeline_state │\n",
              "│ _dlt_version        │\n",
              "│ people              │\n",
              "└─────────────────────┘"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "    id      name  age    city        _dlt_load_id         _dlt_id occupation\n",
              "0    1  Person_1   26  City_A  1706029306.7456656  An8WyXL43/J1GQ       None\n",
              "1    2  Person_2   27  City_A  1706029306.7456656  ZGI1S72CddPbJQ       None\n",
              "2    3  Person_3   28  City_A  1706029306.7456656  +z4Pm5oCykL2Vg       None\n",
              "3    4  Person_4   29  City_A  1706029306.7456656  0Vfr36JHZ34OJA       None\n",
              "4    5  Person_5   30  City_A  1706029306.7456656  aA+9WOclw3YWpg       None\n",
              "5    3  Person_3   33  City_B  1706029307.9851513  mEegoM7n4XujYw      Job_3\n",
              "6    4  Person_4   34  City_B  1706029307.9851513  FPrsrzXgz+E9Fw      Job_4\n",
              "7    5  Person_5   35  City_B  1706029307.9851513  ZaAOBa5EEqXU1Q      Job_5\n",
              "8    6  Person_6   36  City_B  1706029307.9851513  gmcktDnX6y4Fmg      Job_6\n",
              "9    7  Person_7   37  City_B  1706029307.9851513  960gdVKySsa4JA      Job_7\n",
              "10   8  Person_8   38  City_B  1706029307.9851513  +su5IfZQyFEsEw      Job_8"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-164dc4c0-056c-460d-b99f-0582206da3c6\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>id</th>\n",
              "      <th>name</th>\n",
              "      <th>age</th>\n",
              "      <th>city</th>\n",
              "      <th>_dlt_load_id</th>\n",
              "      <th>_dlt_id</th>\n",
              "      <th>occupation</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>Person_1</td>\n",
              "      <td>26</td>\n",
              "      <td>City_A</td>\n",
              "      <td>1706029306.7456656</td>\n",
              "      <td>An8WyXL43/J1GQ</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>Person_2</td>\n",
              "      <td>27</td>\n",
              "      <td>City_A</td>\n",
              "      <td>1706029306.7456656</td>\n",
              "      <td>ZGI1S72CddPbJQ</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>Person_3</td>\n",
              "      <td>28</td>\n",
              "      <td>City_A</td>\n",
              "      <td>1706029306.7456656</td>\n",
              "      <td>+z4Pm5oCykL2Vg</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>Person_4</td>\n",
              "      <td>29</td>\n",
              "      <td>City_A</td>\n",
              "      <td>1706029306.7456656</td>\n",
              "      <td>0Vfr36JHZ34OJA</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>Person_5</td>\n",
              "      <td>30</td>\n",
              "      <td>City_A</td>\n",
              "      <td>1706029306.7456656</td>\n",
              "      <td>aA+9WOclw3YWpg</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>3</td>\n",
              "      <td>Person_3</td>\n",
              "      <td>33</td>\n",
              "      <td>City_B</td>\n",
              "      <td>1706029307.9851513</td>\n",
              "      <td>mEegoM7n4XujYw</td>\n",
              "      <td>Job_3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>4</td>\n",
              "      <td>Person_4</td>\n",
              "      <td>34</td>\n",
              "      <td>City_B</td>\n",
              "      <td>1706029307.9851513</td>\n",
              "      <td>FPrsrzXgz+E9Fw</td>\n",
              "      <td>Job_4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>5</td>\n",
              "      <td>Person_5</td>\n",
              "      <td>35</td>\n",
              "      <td>City_B</td>\n",
              "      <td>1706029307.9851513</td>\n",
              "      <td>ZaAOBa5EEqXU1Q</td>\n",
              "      <td>Job_5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>6</td>\n",
              "      <td>Person_6</td>\n",
              "      <td>36</td>\n",
              "      <td>City_B</td>\n",
              "      <td>1706029307.9851513</td>\n",
              "      <td>gmcktDnX6y4Fmg</td>\n",
              "      <td>Job_6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>7</td>\n",
              "      <td>Person_7</td>\n",
              "      <td>37</td>\n",
              "      <td>City_B</td>\n",
              "      <td>1706029307.9851513</td>\n",
              "      <td>960gdVKySsa4JA</td>\n",
              "      <td>Job_7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>8</td>\n",
              "      <td>Person_8</td>\n",
              "      <td>38</td>\n",
              "      <td>City_B</td>\n",
              "      <td>1706029307.9851513</td>\n",
              "      <td>+su5IfZQyFEsEw</td>\n",
              "      <td>Job_8</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-164dc4c0-056c-460d-b99f-0582206da3c6')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-164dc4c0-056c-460d-b99f-0582206da3c6 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-164dc4c0-056c-460d-b99f-0582206da3c6');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-d353cda7-9937-430a-a4e2-605b8f9fa6ab\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-d353cda7-9937-430a-a4e2-605b8f9fa6ab')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-d353cda7-9937-430a-a4e2-605b8f9fa6ab button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "Sum of ages from generators `people_1()` and `people_2()` combined: 353\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Solution 3: Merge a generator\n",
        "\n",
        "A table's primary key needs to be created from the start, so load your data to a new table with primary key ID.\n",
        "\n",
        "Load your first generator first, and then load the second one with merge. Since they have overlapping IDs, some of the records from the first load should be replaced by the ones from the second load.\n",
        "\n",
        "After loading, you should have a total of 8 records, and ID 3 should have age 33."
      ],
      "metadata": {
        "id": "G-T-jR9qlzdB"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import dlt\n",
        "\n",
        "# Set up a DLT pipeline.\n",
        "# Currently using DuckDB for local testing, but it can be switched to BigQuery for production.\n",
        "generators_pipeline = dlt.pipeline(destination='duckdb', dataset_name='people_merge')\n",
        "\n",
        "# Load data from the first generator `people_1` into 'people_merge' table.\n",
        "# This operation will replace any existing data in the table.\n",
        "# A primary key 'ID' is specified for potential future merge operations.\n",
        "info = generators_pipeline.run(people_1(),\n",
        "                               table_name=\"people_v2\",\n",
        "                               write_disposition=\"replace\",\n",
        "                               primary_key=\"ID\")\n",
        "\n",
        "# Print metadata of the loading process for the first generator.\n",
        "print(f\"{info}\\n\\n\")\n",
        "\n",
        "# Load data from the second generator `people_2` into the same 'people_merge' table.\n",
        "# This operation will merge the new data with existing data based on the primary key 'ID'.\n",
        "info = generators_pipeline.run(people_2(),\n",
        "                               table_name=\"people_merged\",\n",
        "                               write_disposition=\"merge\",\n",
        "                               primary_key=\"ID\")\n",
        "\n",
        "# Print metadata of the loading process for the second generator.\n",
        "print(f\"{info}\\n\\n\")\n",
        "\n",
        "import duckdb\n",
        "\n",
        "# Establish a connection to the DuckDB database created by the pipeline.\n",
        "conn = duckdb.connect(f\"{generators_pipeline.pipeline_name}.duckdb\")\n",
        "\n",
        "# Set the search path to the dataset 'people_merge' and display the available tables.\n",
        "conn.sql(f\"SET search_path = '{generators_pipeline.dataset_name}'\")\n",
        "print('Loaded tables: ')\n",
        "display(conn.sql(\"show tables\"))\n",
        "\n",
        "# Display the merged data from the 'people_merged' table.\n",
        "print(\"\\n\\n\\nData from the 'people_merged' table:\")\n",
        "data = conn.sql(\"SELECT * FROM people_merged\").df()\n",
        "display(data)\n",
        "\n",
        "# Calculate and display the sum of ages from the merged data in 'people_merged' table.\n",
        "sum_of_ages_p1_p2 = conn.execute(\"SELECT SUM(age) FROM people_merged\").fetchone()[0]\n",
        "print(f\"\\n\\nSum of ages of people in generator `people_1()` merged with generator `people_2()` is: {sum_of_ages_p1_p2}\")\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 773
        },
        "id": "rXR-IN85kBtq",
        "outputId": "c74a7ab7-aa77-4445-c2bc-e782054a7201"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Pipeline dlt_colab_kernel_launcher load step completed in 0.24 seconds\n",
            "1 load package(s) were loaded to destination duckdb and into dataset people_merge\n",
            "The duckdb destination used duckdb:////content/dlt_colab_kernel_launcher.duckdb location to store data\n",
            "Load package 1706030294.0522 is LOADED and contains no failed jobs\n",
            "\n",
            "\n",
            "Pipeline dlt_colab_kernel_launcher load step completed in 0.42 seconds\n",
            "1 load package(s) were loaded to destination duckdb and into dataset people_merge\n",
            "The duckdb destination used duckdb:////content/dlt_colab_kernel_launcher.duckdb location to store data\n",
            "Load package 1706030294.7037766 is LOADED and contains no failed jobs\n",
            "\n",
            "\n",
            "Loaded tables: \n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "┌─────────────────────┐\n",
              "│        name         │\n",
              "│       varchar       │\n",
              "├─────────────────────┤\n",
              "│ _dlt_loads          │\n",
              "│ _dlt_pipeline_state │\n",
              "│ _dlt_version        │\n",
              "│ people_merged       │\n",
              "│ people_v2           │\n",
              "└─────────────────────┘"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\n",
            "Data from the 'people_merged' table:\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "   id      name  age    city occupation        _dlt_load_id         _dlt_id\n",
              "0   8  Person_8   38  City_B      Job_8  1706030294.7037766  Q1k+DIAjXLL7cg\n",
              "1   4  Person_4   34  City_B      Job_4  1706030294.7037766  ewlZ3LjULEchiQ\n",
              "2   5  Person_5   35  City_B      Job_5  1706030294.7037766  X+LfQEa/X8GU9w\n",
              "3   7  Person_7   37  City_B      Job_7  1706030294.7037766  lQT0h7IL7E/wxg\n",
              "4   3  Person_3   33  City_B      Job_3  1706030294.7037766  gRBswCo8B/DJmw\n",
              "5   6  Person_6   36  City_B      Job_6  1706030294.7037766  M3IbNKfZZCtbcQ"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-2f5274be-509c-41be-924d-49590376474d\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>id</th>\n",
              "      <th>name</th>\n",
              "      <th>age</th>\n",
              "      <th>city</th>\n",
              "      <th>occupation</th>\n",
              "      <th>_dlt_load_id</th>\n",
              "      <th>_dlt_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>8</td>\n",
              "      <td>Person_8</td>\n",
              "      <td>38</td>\n",
              "      <td>City_B</td>\n",
              "      <td>Job_8</td>\n",
              "      <td>1706030294.7037766</td>\n",
              "      <td>Q1k+DIAjXLL7cg</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>4</td>\n",
              "      <td>Person_4</td>\n",
              "      <td>34</td>\n",
              "      <td>City_B</td>\n",
              "      <td>Job_4</td>\n",
              "      <td>1706030294.7037766</td>\n",
              "      <td>ewlZ3LjULEchiQ</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>5</td>\n",
              "      <td>Person_5</td>\n",
              "      <td>35</td>\n",
              "      <td>City_B</td>\n",
              "      <td>Job_5</td>\n",
              "      <td>1706030294.7037766</td>\n",
              "      <td>X+LfQEa/X8GU9w</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>7</td>\n",
              "      <td>Person_7</td>\n",
              "      <td>37</td>\n",
              "      <td>City_B</td>\n",
              "      <td>Job_7</td>\n",
              "      <td>1706030294.7037766</td>\n",
              "      <td>lQT0h7IL7E/wxg</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>3</td>\n",
              "      <td>Person_3</td>\n",
              "      <td>33</td>\n",
              "      <td>City_B</td>\n",
              "      <td>Job_3</td>\n",
              "      <td>1706030294.7037766</td>\n",
              "      <td>gRBswCo8B/DJmw</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>6</td>\n",
              "      <td>Person_6</td>\n",
              "      <td>36</td>\n",
              "      <td>City_B</td>\n",
              "      <td>Job_6</td>\n",
              "      <td>1706030294.7037766</td>\n",
              "      <td>M3IbNKfZZCtbcQ</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2f5274be-509c-41be-924d-49590376474d')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-2f5274be-509c-41be-924d-49590376474d button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-2f5274be-509c-41be-924d-49590376474d');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-59a3fb69-8001-41be-ac63-c616dc356aab\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-59a3fb69-8001-41be-ac63-c616dc356aab')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-59a3fb69-8001-41be-ac63-c616dc356aab button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "Sum of ages of people in generator `people_1()` merged with generator `people_2()` is: 213\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "TApfkuNKtlt3"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}